"""
Copyright (c) 2023 - present SKbioinfo
"""
from flask_restful import Resource
from flask import request
from flask_login import login_required,current_user

from apps.NGS_API.common.common_time import *
from apps.NGS_API.common.common_generate_id import *
from apps.NGS_API.record.history_util import *
from apps.NGS_API.utils.log_helper import lg
from apps.NGS_API.fileup.excel_format import form_to_database as ftd
from apps.NGS_API.fileup.excel_format import add_form as aform
from apps.NGS_API.fileup.excel_format import del_form as dform
from apps.NGS_API.fileup.excel_format import out_form as uform
from apps.NGS_API.fileup.excel_format import up_form as pform
from apps.NGS_API.fileup.utils import data_checker
from apps.NGS_API.fileup.utils import fileup_log_record
from apps.NGS_API.extraction.db_extraction import *
from apps.NGS_API.connector.db_common import *
import pandas as pd
pm=operation_monitor(table_name=ngs_op_log["name"])

class data_table(object):
    def __init__(self,db_table):
        self.db_table=db_table
        
    def table_add(self,file_df:pd.DataFrame(),up_format:dict,auto_id={},**kwargs):
        if auto_id == {}:
            pass
        else:
            gen = id_generator(name=auto_id["table_name"])
            id = gen.generate_id(col=auto_id["col"])[auto_id["col"]]
            prefix = gen.generate_prefix(tag=auto_id["prefix"])
        for index in range(0,file_df.shape[0]):
            ### initialization ###
            add_sql=""
            table_columns=""
            table_values=""
            ### SQL: single item insert sql ###
            for column,name in up_format.items():
                table_columns += str(column) + ",\n"
                table_values += "'" + str(file_df.iloc[index][name]) + "'" + ",\n"
            ### record modify time ###
            table_columns=table_columns + "modify_time" 
            table_values=table_values + "'" + get_current_time() + "'"
            ### record estra infos ###
            if kwargs["extra"] and kwargs["extra"] != {}:
                table_columns = table_columns + "," + kwargs["extra"]["table_columns"]
                table_values = table_values + "," + kwargs["extra"]["table_values"]
            ### assign auto id ###
            if auto_id == {}:
                pass
            else:
                id = int(id) + 1
                table_columns = table_columns + "," + auto_id["col"]
                table_values = table_values + ",'" + prefix + str(id) + "'"
            ## write insert sql ###
            add_sql='''
                INSERT INTO {db_table}(
                    {table_columns}
                ) VALUES (
                    {table_values}
                )
            '''.format(
                db_table=self.db_table,
                table_columns=table_columns,
                table_values=table_values
            )
            ### add table infos to database ###
            add_info(add_sql=add_sql)
    
    def table_delete(self,col_name,file_df:pd.DataFrame(),up_format:dict):
        for index in range(0,file_df.shape[0]):
            ### SQL: single item delete sql ###
            delete_sql='''
                DELETE FROM {db_table} WHERE {db_name}=\"{col_value}\"
            '''.format(
                    db_table=self.db_table,
                    db_name=col_name,
                    col_value=file_df.iloc[index][up_format[col_name]]
            )
            delete_info(delete_sql=delete_sql)
                
    def table_update(self,col,col_names,file_df:pd.DataFrame(),up_format:dict):
        for index in range(0,file_df.shape[0]):
            ### SQL: single item update sql ###
            update_cols=""
            for name in col_names:
                update_cols=update_cols + name + "=" + str(file_df.iloc[index][up_format[name]]) + ","
            update_cols=update_cols.rstrip(",")
            ### SQL: update sql script ###
            update_sql='''
                UPDATE {table_name} SET
                    {updata_cols}
                WHERE {col}=\'{sample_id}\'
            '''.format(
                    table_name=self.db_table,
                    updata_cols=update_cols,
                    col=col,
                    sample_id=file_df.iloc[index][up_format[col]]
            )
            ### SQL: update table infos ###
            update_info(update_sql=update_sql)

def sample_instock(form,content,**kwargs):
    instock_cols ='''
        primary_sample_id,primary_project_id,
        project_id,sample_id,
        stock_label,stock_locale,instock_type,
        instock_user,instock_check_user,
        instock_amount,instock_time,
        cur_amount,outstock_type,
        outstock_user,outstock_check_user,
        outstock_amount,outstock_time,
        create_time,modify_time,
        stock_id,status
    '''
    col_values ='''
        \"{primary_sample_id}\",\"{primary_project_id}\",
        \"{project_id}\",\"{sample_id}\",\"{stock_label}\",
        \"{stock_locale}\",{instock_type},
        \"{instock_user}\",\"{instock_check_user}\",
        {instock_amount},\"{instock_time}\",
        {cur_amount},{outstock_type},
        \"{outstock_user}\",\"{outstock_check_user}\",
        {outstock_amount},\"{outstock_time}\",
        \"{create_time}\",\"{modify_time}\",
        \"{stock_id}\",{status}
    '''.format(
            project_id=kwargs["project_id"],
            sample_id=kwargs["sample_id"],
            primary_sample_id=content[form["primary_sample_id"]], 
            primary_project_id=content[form["primary_project_id"]],
            stock_label=content[form["stock_label"]],
            stock_locale=content[form["stock_locale"]], 
            instock_type=0, instock_user=current_user.username,
            instock_check_user=current_user.username,
            instock_amount=0, instock_time=get_current_time(),
            cur_amount=0, outstock_type =-1,
            outstock_user="", outstock_check_user="",
            outstock_amount=0, outstock_time=get_current_time(),
            create_time=get_current_time(), modify_time=get_current_time(),
            stock_id=kwargs["stock_id"],status=0
    )
    ### SQL: instock sample ###
    sample_instock_sql='''
        INSERT INTO {table_name} (
            {cols}
        ) VALUES (
            {col_values}
        )
    '''.format(
        table_name=sample_stock_db["name"],
        cols=instock_cols,
        col_values=col_values
    )
    add_info(sample_instock_sql)
            
class interfaceUploadAdd(Resource):
    @login_required
    def post(self,name):
        ### read data from front-end ###
        file=request.files.get("file")
        file_df=pd.read_excel(file)
        file_df = file_df.dropna(axis=0, how='any',subset=None, inplace=False)
        ### upload data ###
        if name == "extraction":
            try:
                ### data colunms name => excel title name ###
                extraction=aform.extraction
                extraction_table=data_table(db_table=ftd[name])
                extraction_table.table_add(
                    file_df=file_df,
                    up_format=extraction
                )
            except Exception as e:
                lg.error(e) 
        elif name == "bioana":
            try:
                ### data colunms name => excel title name ###
                bioana=aform.bioana
                bioana_table=data_table(db_table=ftd[name])
                checker=data_checker(table_name=bioana_db["name"],file_df=file_df)
                final_df=checker.remove_duplicated_data(
                    mapping = {
                        "analysis_id":bioana["analysis_id"],
                        "project_id":bioana["project_id"]
                    }
                )
                bioana_table.table_add(
                    file_df=final_df,
                    up_format=bioana,
                    extra = {
                        "table_columns":"operate_start_time,operate_end_time,create_time",
                        "table_values":'''"{op_st_time}","{op_ed_time}","{create_time}"'''.format(
                            op_st_time = get_current_time(),
                            op_ed_time = get_current_time(),
                            create_time = get_current_time()
                        )
                    }
                )
            except Exception as e:
                lg.error(e)  
        elif name == "sample":
            try:
                ### upload sample table ###      
                sample=aform.sample
                ### remove duplicated data ###
                checker=data_checker(table_name=sample_stock_db["name"],file_df=file_df)
                final_df=checker.remove_duplicated_data(
                    mapping = {
                        "primary_project_id":sample["primary_project_id"],
                        "primary_sample_id":sample["primary_sample_id"]
                    }
                )
                if final_df.empty:
                    pass
                else:
                    ### sample ID generator ###
                    sam_gen = id_generator(name=sample_db["name"])
                    sam_id = int(sam_gen.generate_id(col="sample_id")["sample_id"])
                    sam_prefix = sam_gen.generate_prefix(tag="sample")
                    ### stock ID generator ###
                    stc_gen = id_generator(name=sample_stock_db["name"])
                    stc_id = int(stc_gen.generate_id(col="stock_id")["stock_id"])
                    stc_prefix = sam_gen.generate_prefix(tag="stock")
                    for index,content in final_df.iterrows():
                        ### SQL: query project id ###
                        project_id_sql = '''
                            SELECT 
                                {col}
                            FROM {table_name}
                            WHERE {col_name} = \"{col_value}\"
                        '''.format(
                            col="project_id",
                            table_name=project_db["name"],
                            col_name="primary_id",
                            col_value=content["项目编号"]
                        )
                        ### SQL: query results ###
                        result = query_info(project_id_sql)
                        if len(result) == 0:
                            pass
                        else:
                            ### initalization ###
                            proj_id = result[0][0]
                            ### SQL: sample instock ###
                            table_columns = ""
                            table_values = ""
                            for column,name in sample.items():
                                table_columns += str(column) + ",\n"
                                table_values += "'" + str(final_df.iloc[index][name]) + "'" + ",\n"
                            ### automatic generate ID ###
                            sam_id = sam_id + 1
                            ### SQL: add auto increasing id to database ###
                            table_columns = table_columns + "project_id,sample_id,modify_time,create_time,init_concen,init_volume,init_amount,cur_amount,unit\n"
                            table_values = table_values + "\"{proj_id}\",\"{sam_id}\",\"{md_time}\",\"{cre_time}\",\"{init_concen}\",\"{init_volume}\",\"{init_amount}\",\"{cur_amount}\",\"{unit}\"".format(
                                proj_id = proj_id,
                                sam_id = str(sam_prefix) + str(sam_id),
                                stc_id = str(stc_prefix) + str(stc_id),
                                md_time = get_current_time(),
                                cre_time = get_current_time(),
                                init_concen = float(content["初始样品浓度"].split("|")[0]),
                                init_volume = float(content["初始样品体积"].split("|")[0]),
                                init_amount = float(content["初始样品体积"].split("|")[0]),
                                cur_amount = float(content["初始样品体积"].split("|")[0]),
                                unit = content["初始样品浓度"].split("|")[1]
                                 + "," + content["初始样品体积"].split("|")[1],
                            )
                            ### SQL: sample record sql ###
                            sample_record_sql='''
                                INSERT INTO {table_name} (
                                        {cols}
                                    ) VALUES (
                                        {col_values}
                                    )
                                '''.format(
                                    table_name=sample_db["name"],
                                    cols=table_columns,
                                    col_values=table_values
                                )
                            add_info(sample_record_sql)
                            '''
                                SQL: instock samples amount
                            '''
                            stc_id = stc_id + 1
                            sample_instock(
                                form=aform.stock,
                                content=content,
                                project_id=proj_id,
                                sample_id=str(sam_prefix) + str(sam_id),
                                stock_id=str(stc_prefix) + str(stc_id)
                            )
                            flog=fileup_log_record(
                                row=content,
                                form=sample_log
                            )
                            flog.instock_log()
            except Exception as e:
                lg.error(e)
        elif name == "stock":
            try:
                stock=aform.stock
                stock_table=data_table(db_table=ftd[name])
                stock_table.table_add(
                    file_df=file_df,
                    up_format=stock,
                )     
            except Exception as e:
                lg.error(e)
        elif name == "ngsLib":
            try:
                ngsLib=aform.ngsLib
                ngsLib_table=data_table(db_table=ftd[name])
                ngsLib_table.table_add(
                        file_df=file_df,
                        up_format=ngsLib,
                )
            except Exception as e:
                lg.error(e)
        elif name == "project":
            try:
                project=aform.project
                project_table=data_table(db_table=ftd[name])
                checker = data_checker(table_name=project_db["name"],file_df=file_df)
                final_df = checker.remove_duplicated_data(
                    mapping = {
                        "primary_id":project["primary_id"],
                        "project_tag":project["project_tag"]
                    } 
                )
                if final_df.empty:
                    print("duplicate upload data")
                else:
                    project_table.table_add(
                        file_df=final_df,
                        up_format=project,
                        auto_id= {
                            "table_name":ftd[name],
                            "col": "project_id",
                            "prefix":"project"
                        },
                        extra = {
                            "table_columns":"create_time,status",
                            "table_values":"'" + get_current_time() + "'," + "0" 
                        }
                    )
            except Exception as e:
                lg.error(e)
        return name

class interfaceUploadDel(Resource):
    @login_required
    def post(self,name):
        file=request.files.get("file")
        file_df=pd.read_excel(file)
        col_name="sample_id"
        if name == "extraction":
            try:
                extraction=dform.extraction
                extraction_table=data_table(db_table=ftd[name])
                extraction_table.table_delete(
                    file_df=file_df,
                    up_format=extraction,
                    col_name=col_name
                )
            except Exception as e:
                lg.error(e)
        elif name == "sample":
            try:
                sample=dform.sample
                sample_table=data_table(db_table=ftd[name])
                sample_table=sample_table.table_delete(
                    file_df=file_df,
                    up_format=sample,
                    col_name=col_name
                )
            except Exception as e:
                lg.error(e)
        elif name == "ngsLib":
            try:
                ngsLib=dform.extraction
                ngsLib_table=data_table(db_table=ftd[name])
                ngsLib_table.table_delete(
                    file_df=file_df,
                    up_format=ngsLib,
                    col_name=col_name
                )
            except Exception as e:
                lg.error(e)
        elif name == "stock":
            try:
                stock=dform.stock
                stock_table=data_table(db_table=ftd[name])
                stock_table.table_delete(
                    file_df=file_df,
                    up_format=stock,
                    col_name=col_name
                )
            except Exception as e:
                lg.error(e)
        return name

class interfaceUploadUpd(Resource):
    @login_required
    def post(self,name):
        ### read data from front-end ###
        file=request.files.get("file")
        file_df=pd.read_excel(file)
        if name == "sample":
            try:
                col="sample_id"
                col_names=[
                    "init_amount",
                    "cur_amount"
                ]
                sample=pform.sample
                sample_table=data_table(db_table=ftd[name])
                sample_table.table_update(
                    file_df=file_df,
                    up_format=sample,
                    col_names=col_names,
                    col=col
                )
            except Exception as e:
                lg.error(e)

class interfaceUploadOut(Resource):
    @login_required
    def post(self,name):
        ### read data from front-end ###
        file=request.files.get("file")
        file_df=pd.read_excel(file)
        col_name="sample_id"
        if name == "sample":
            try:
                sample=uform.sample
                sample_out_table=data_table(db_table=ftd[name])
                sample_out_table.table_delete(
                    file_df=file_df,
                    up_format=sample,
                    col_name=col_name
                )
            except Exception as e:
                lg.error(e)